Tabelas do PostgreSQL por ordenada pela dependencia entre elas

#tech/postgresql #public

with recursive fk_tree as (
    -- All tables not referencing anything else
    select
        t.oid as reloid,
        t.relname as table_name,
        s.nspname as schema_name,
        null :: name as referenced_table_name,
        null :: name as referenced_schema_name,
        1 as level
    from
        pg_class t
        join pg_namespace s on s.oid = t.relnamespace
    where
        relkind = 'r'
        and not exists (
            select
                *
            from
                pg_constraint
            where
                contype = 'f'
                and conrelid = t.oid
        )
        and s.nspname = 'public' -- limit to one schema
        and t.relname not like 'core_activitylog%'
        and t.relname not like 'auth_%' -- feito
        and t.relname not like 'taggit%' -- feito
        and t.relname not like 'django_%' -- feito
        and t.relname not like 'social_auth_%' -- feito
        and t.relname not like 'explorer_%' -- feito
        and t.relname not like 'ext_%' -- fazendo
        and t.relname not like 'blog_%' -- feito (e core_cidade*)
        and t.relname not like 'glossario_%' -- feito
        and t.relname not like 'multi_armed_%' -- feito
        and t.relname not like 'third_party_%' -- feito
        and t.relname not like 'aux_%' -- feito
        and t.relname not like 'sap_%' -- feito
        and t.relname not like 'health_check_%' -- feito
    union
    all
    select
        ref.oid,
        ref.relname,
        rs.nspname,
        p.table_name,
        p.schema_name,
        p.level + 1
    from
        pg_class ref
        join pg_namespace rs on rs.oid = ref.relnamespace
        join pg_constraint c on c.contype = 'f'
        and c.conrelid = ref.oid
        join fk_tree p on p.reloid = c.confrelid
    where
        ref.oid != p.reloid -- do not enter to tables referencing theirselves.
        and ref.relname not like 'core_activitylog%'
        and ref.relname not like 'auth_%' -- feito
        and ref.relname not like 'taggit%' -- feito
        and ref.relname not like 'django_%' -- feito
        and ref.relname not like 'social_auth_%' -- feito
        and ref.relname not like 'explorer_%' -- feito
        and ref.relname not like 'ext_%' -- fazendo
        and ref.relname not like 'blog_%' -- feito (e core_cidade*)
        and ref.relname not like 'glossario_%' -- feito
        and ref.relname not like 'multi_armed_%' -- feito
        and ref.relname not like 'third_party_%' -- feito
        and ref.relname not like 'aux_%' -- feito
        and ref.relname not like 'sap_%' -- feito
        and ref.relname not like 'health_check_%' -- feito
),
all_tables as (
    -- this picks the highest level for each table
    select
        schema_name,
        table_name,
        level,
        row_number() over (
            partition by schema_name,
            table_name
            order by
                level desc
        ) as last_table_row
    from
        fk_tree
)
select
    schema_name,
    table_name,
    level
from
    all_tables at
where
    last_table_row = 1
order by
    level;

with aux as (
    select
        t.oid as reloid,
        t.relname as tablename,
        s.nspname as schema_name,
        null :: name as referenced_table_name,
        null :: name as referenced_schema_name,
        1 as level
    from
        pg_class t
        join pg_namespace s on s.oid = t.relnamespace
    where
        relkind = 'r'
        and not exists (
            select
                *
            from
                pg_constraint
            where
                contype = 'f'
                and conrelid = t.oid
        )
        and s.nspname = 'public'
)
select
    *
from
    aux